package org.dreamwork.android.persistence;

import android.app.Application;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import org.dreamwork.db.ScriptParser;
import org.dreamwork.persistence.DatabaseSchema;
import org.dreamwork.persistence.ISchemaField;
import org.dreamwork.persistence.ReflectUtil;
import org.dreamwork.util.ITypedMap;
import org.dreamwork.util.StringUtil;
import org.dreamwork.util.TypedHashMap;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * SQLite 工具类，配合 {@link DatabaseSchema} 使用，将大量减少代码量. 非常轻量级的 O/R Mapping小工具.
 *
 * <p>
 *     使用 <a href="https://git.oschina.net/seth_yang/dreamwork/tree/master/modules/assistant">Simple Persistence GUI tool</a>
 *     来生成模型类
 * </p>
 * <p>Created by game on 2016/9/7</p>
 */
public class SQLite {
    private static SQLiteDatabase db;
    private static ReflectUtil ref = new ReflectUtil ();
    private static Map<Class<?>, Map<String, Field>> cache = new HashMap<> ();
    private static Map<Class<?>, Class<? extends DatabaseSchema>> mapping = new HashMap<> ();

    private static boolean D;

    private static final String AUTOINCREMENT_SQL = "SELECT seq FROM sqlite_sequence WHERE name = ?";
    private static final String TAG = "org.dreamwork.android.persistence";
    private static final SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd", Locale.getDefault ());
    private static final SimpleDateFormat stf = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss", Locale.getDefault ());

    public static boolean isInitialed () {
        return db != null;
    }

    /**
     * 初始化.
     * <p>通常在 {@link android.app.Application}.{@link Application#onCreate()}中调用
     * <pre>
     *public class MyApp extends Application {
     *    &#64;Override
     *    public void onCreate () {
     *        super.onCreate ();
     *
     *        SQLite.init (new DatabaseHelper (this));
     *    }
     *}
     * </pre>
     * </p>
     * @param helper
     */
    public static void init (SQLiteOpenHelper helper) {
        db = helper.getWritableDatabase ();
    }

    public static void init (SQLiteDatabase db) {
        SQLite.db = db;
    }

    /**
     * 使能调试信息
     */
    public static void enableDebug () {
        D = true;
    }

    /**
     * 关闭调试信息
     */
    public static void disableDebug () {
        D = false;
    }

    /**
     * 检查某个模型类映射的表是否存在
     * @param type 模型类
     * @return 若存在返回 <code>true</code>，否则<code>false</code>
     */
    public static boolean isSchemaExists (Class<?> type) {
        DatabaseSchema schema = ref.map (type);
        if (schema != null) {
            String sql = "SELECT COUNT(*) FROM sqlite_master where type = 'table' and name = ?";
            try (Cursor c = db.rawQuery (sql, new String[] {schema.getTableName ()})) {
                if (c.moveToNext ()) {
                    int count = c.getInt (0);
                    return count > 0;
                }
            }
        }

        return false;
    }

    /**
     * 在数据库中生成所有映射的表
     */
    public static void createSchemas () {
        for (DatabaseSchema ds : DatabaseSchema.MAP.values ()) {
            String ddl = ds.getCreateDDL ();
            if (D) {
                Log.d (TAG, "creating the table " + ds.getTableName () + " using: " + ddl);
            }
            db.execSQL (ddl);
        }
    }

    /**
     * 清空某个模型类映射的表的所有记录
     * @param type 模型类
     */
    public static void clear (Class<?> type) {
        DatabaseSchema schema = ref.map (type);
        if (schema != null) {
            String drop = "DROP TABLE " + schema.getTableName ();
            db.execSQL (drop);
            db.execSQL (schema.getCreateDDL ());
        }
    }

    /**
     * 执行原始SQL语句.
     * <p>这是 {@link SQLiteDatabase#execSQL(String, Object[])}的便捷方法</p>
     * @param sql 要执行的 SQL 语句
     * @param args SQL语句参数，如果有的话
     */
    public static void execute (String sql, Object... args) {
        db.execSQL (sql, args);
    }

    /**
     * 执行脚本文件. 通常用来初始化数据库
     * @param in 脚本文件的输入流
     * @throws IOException
     */
    public static void execute (InputStream in) throws IOException {
        List<String> list = new ScriptParser (in).parse ();
        if (list != null && !list.isEmpty ()) for (String sql : list) {
            if (!StringUtil.isEmpty (sql))
                db.execSQL (sql.trim ());
        }
    }

    /**
     * 执行查询.
     *
     * 一个 {@link SQLiteDatabase#rawQuery(String, String[])} 的便捷方式.
     * @param sql sql语句
     * @param selectionArgs 查询参数
     * @return {@link Cursor} 游标
     */
    public static Cursor executeQuery (String sql, String... selectionArgs) {
        return db.rawQuery (sql, selectionArgs);
    }

    /**
     * 执行查询，并返回 {@link ITypedMap} 集合
     * @param sql sql 语句
     * @param selectionArgs 查询参数
     * @return {@link ITypedMap} 集合
     */
    public static ArrayList<ITypedMap> list (String sql, String... selectionArgs) {
        try (Cursor c = executeQuery (sql, selectionArgs)) {
            ArrayList<ITypedMap> list = new ArrayList<> ();
            while (c.moveToNext ()) {
                list.add (build (c));
            }

            return list;
        }
    }

    /**
     * 执行查询，并试图返回特定的模型类
     * @param type 模型类
     * @param sql sql语句
     * @param selectionArgs 查询参数
     * @param <T> 模型类型, 必须要(通过{@link DatabaseSchema})映射到数据库
     * @return 特定模型类的列表
     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     */
    public static<T> ArrayList<T> list (Class<T> type, String sql, String... selectionArgs) {
        try (Cursor c = executeQuery (sql, selectionArgs)) {
            ArrayList<T> list = new ArrayList<> ();
            while (c.moveToNext ()) {
                list.add (build (c, type));
            }

            return list;
        } catch (Exception ex) {
            throw new RuntimeException (ex);
        }
    }

    /**
     * {@link SQLiteDatabase#query(String, String[], String, String[], String, String, String, String)} 的便捷方法
     */
    public static Cursor executeQuery (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
        return db.query (table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
    }

    /**
     * {@link SQLiteDatabase#query(String, String[], String, String[], String, String, String, String)} 的便捷方法，
     * 但是返回 {@link ITypedMap} 列表
     */
    public static ArrayList<ITypedMap> list (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
        try (Cursor c = executeQuery (table, columns, selection, selectionArgs, groupBy, having, orderBy, limit)) {
            ArrayList<ITypedMap> list = new ArrayList<> ();
            while (c.moveToNext ()) {
                list.add (build (c));
            }

            return list;
        }
    }

    /**
     * {@link SQLiteDatabase#query(String, String[], String, String[], String, String, String, String)} 的便捷方法，
     * 但是返回 特定的模型类列表
     * @param type 模型类
     * @param <T> 模型类类型，必须（通过 {@link DatabaseSchema})绑定到数据库
     * @return 特定模型类的列表
     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     */
    public static<T> ArrayList<T> list (Class<T> type, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
        try (Cursor c = executeQuery (table, columns, selection, selectionArgs, groupBy, having, orderBy, limit)) {
            ArrayList<T> list = new ArrayList<> ();
            while (c.moveToNext ()) {
                list.add (build (c, type));
            }

            return list;
        } catch (Exception ex) {
            throw new RuntimeException (ex);
        }
    }

    /**
     * 更新数据库， {@link SQLiteDatabase#execSQL(String, Object[])} 针对更新语句的便捷方法
     * @param sql sql 语句
     * @param args 更新参数
     */
    public static void executeUpdate (String sql, Object... args) {
        if (args.length > 0) {
            db.execSQL (sql, args);
        } else {
            db.execSQL (sql);
        }
    }

    /**
     * 保存对象到数据库.
     * @param item 需要保存的对象，该对象必须已经绑定到数据库
     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     *
     */
    public static void save (Object item) {
        save (item, true);
    }

    /**
     * 保存对象到数据库，并可能返回数据库产生的主键.
     *
     * <p>
     * 因为需要从数据反选出主键，故效率较低，若批量执行时（比如在循环中），应将 <code>fetchPK</code> 参数置false.
     * 或直接使用 {@link #save(Collection)}进行批量保存
     * </p>
     * @param item 需要保存的对象，该对象必须已经绑定到数据库
     * @param fetchPK 若true,从数据库获取刚刚产生的主键（如果可能）
     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     */
    public static void save (Object item, boolean fetchPK) {
        if (item == null) {
            throw new NullPointerException ("item is null");
        }

        try {
            Class<?> type = item.getClass ();
            DatabaseSchema schema = ref.map (type);
            Object[] parameters = ref.getInsertParameters (schema, item, type);
            if (fetchPK) {
                db.beginTransaction ();
            }
            db.execSQL (schema.getInsertSQL (), parameters);
            if (fetchPK) {
                Map<String, Field> map = ref.getTypedList (type);
                for (Field field : map.values ()) {
                    ISchemaField sf = field.getAnnotation (ISchemaField.class);
                    if (sf.autoincrement ()) {
                        Cursor cursor = null;
                        try {
                            cursor = db.rawQuery (AUTOINCREMENT_SQL, new String[]{schema.getTableName ()});
                            if (cursor.moveToNext ()) {
                                Class<?> cls = field.getType ();
                                Object o = null;
                                if (cls == int.class || cls == Integer.class) {
                                    o = cursor.getInt (0);
                                } else if (cls == long.class || cls == Long.class) {
                                    o = cursor.getLong (0);
                                }

                                if (o != null) {
                                    field.set (item, o);
                                }
                            }
                        } finally {
                            if (cursor != null) {
                                cursor.close ();
                            }
                        }
                        break;
                    }
                }
                db.setTransactionSuccessful ();
            }
        } catch (Exception ex) {
            throw new SQLException (ex.getMessage (), ex);
        } finally {
            if (fetchPK) {
                db.endTransaction ();
            }
        }
    }

    /**
     * 批量保存模型对象
     * @param items 模型对象集合
     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     */
    public static void save (Collection<?> items) {
        try {
            db.beginTransaction ();
            for (Object dbo : items) {
                save (dbo, false);
            }
            db.setTransactionSuccessful ();
        } finally {
            db.endTransaction ();
        }
    }

    /**
     * 根据模型对象的主键从数据库中查询对应记录
     * @param type 模型类
     * @param key  主键值
     * @param <T>  模型类型
     * @return     该主键指向的对象
     * @exception  RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     */
    public static<T> T getByPK (Class<T> type, Object key) {
        DatabaseSchema schema = ref.map (type);
        try (Cursor cursor = db.query (
                schema.getTableName (),
                schema.getFields (),
                schema.getPrimaryKeyName () + " = ?",
                new String []{String.valueOf (key)},
                null,
                null,
                null
        )) {
            if (cursor.moveToNext ()) {
                try {
                    return build (cursor, type);
                } catch (Exception ex) {
                    return null;
                }
            }
            return null;
        }
    }

    /**
     * 无条件的从数据库中选中指定模型类的所有记录
     * @param type 模型类
     * @param <T>  模型类型
     * @return     该模型的所有记录
     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     */
    public static<T> ArrayList<T> get (Class<T> type) {
        return get (type, null, null);
    }

//    /**
//     * 从数据库中选择指定模型类的记录
//     * @param type  模型类
//     * @param selection 查询条件
//     * @param args 查询条件参数
//     * @param <T> 模型类型
//     * @return 所有符合条件的记录
//     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
//     */
//    public static<T> ArrayList<T> get (Class<T> type, String selection, String... args) {
//        return get (type, selection, null, args);
//    }

    /**
     * 从数据库中选择指定模型类的记录，并以指定的顺序返回
     * @param type  模型类
     * @param selection 查询条件
     * @param args 查询条件参数
     * @param <T> 模型类型
     * @return 所有符合条件的记录
     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     */
    @SuppressWarnings ("unchecked")
    public static<T> ArrayList<T> get (Class<T> type, String selection, String order, String... args) {
        DatabaseSchema schema = ref.map (type);
        try (Cursor cursor = db.query (
                    schema.getTableName (), schema.getFields (), selection, args, null, null, order
            )) {
            ArrayList<T> list = new ArrayList<> ();
            while (cursor.moveToNext ()) {
                list.add (build (cursor, type));
            }
            return list;
        } catch (Exception ex) {
            throw new SQLException (ex.getMessage (), ex);
        }
    }

    /**
     * 更新模型对象
     * @param o 模型对象
     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     */
    public static void update (Object o) {
        if (o == null) {
            return;
        }

        Class<?> type = o.getClass ();
        DatabaseSchema schema = ref.map (o.getClass ());
        StringBuilder builder = new StringBuilder ();
        Map<String, Field> fields = ref.getTypedList (type);
        int index = 0;
        Object[] values = new Object[fields.size ()];
        Field pk = null;
        String pkName = null;
        for (String name : schema.getFields ()) {
            Field field = fields.get (name);
            if (field == null) {
                values [index ++] = null;
            } else {
                ISchemaField sf = field.getAnnotation (ISchemaField.class);
                if (sf.id ()) {
                    pk     = field;
                    pkName = name;
                } else {
                    try {
                        values [index ++] = ref.getValue (field, o);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace ();
                    }

                    if (builder.length () > 0) {
                        builder.append (", ");
                    }
                    builder.append (name).append (" = ?");
                }
            }
        }

        if (pk != null) {
            try {
                values [values.length - 1] = ref.getValue (pk, o);
            } catch (IllegalAccessException e) {
                e.printStackTrace ();
            }
        }

        String sql = "UPDATE " + schema.getTableName () + " SET " + builder;
        if (!StringUtil.isEmpty (pkName)) {
            sql += " WHERE " + pkName + " = ?";
        }
        if (D) {
            Log.d (TAG, "The sql is: " + sql);
            Log.d (TAG, "The values: " + Arrays.toString (values));
        }
        db.execSQL (sql, values);
    }

    /**
     * 根据SQLite 游标构建模型对象
     * @param cursor 游标
     * @param type 模型类
     * @param <T> 模型类型
     * @return 构建完成的模型对象，可能是 null
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @exception RuntimeException 若模型未映射，将抛出 The type is not mapped 异常
     */
    @SuppressWarnings ("unchecked")
    private static<T> T build (Cursor cursor, Class<T> type) throws IllegalAccessException, InstantiationException {
        T o = type.newInstance ();
        int count = cursor.getColumnCount ();
        Map<String, Field> map = ref.getTypedList (type);
        for (int i = 0; i < count; i ++) {
            String name = cursor.getColumnName (i);
            Field field = map.get (name);
            if (field == null) {
                continue;
            }

            Class<?> c = field.getType ();
            if (c.isAssignableFrom (String.class)) {
                field.set (o, cursor.getString (i));
            } else if (c == int.class || c == Integer.class) {
                field.set (o, cursor.getInt (i));
            } else if (c == boolean.class || c == Boolean.class) {
                String tmp = cursor.getString (i);
                boolean value = false;
                if (!StringUtil.isEmpty (tmp)) {
                    tmp = tmp.trim ().toLowerCase ();
                    if ("t".equals (tmp) || "true".equals ("tmp") || "yes".equals (tmp) || "y".equals (tmp)) {
                        value = true;
                    } else {
                        try {
                            int i_value = cursor.getInt (i);
                            value = (i_value != 0);
                        } catch (Exception ex) {
                            // ignore
                        }
                    }
                    field.set (o, value);
                }
            } else if (c == long.class || c == Long.class) {
                field.set (o, cursor.getLong (i));
            } else if (c == float.class || c == Float.class) {
                field.set (o, cursor.getFloat (i));
            } else if (c == short.class || c == Short.class) {
                field.set (o, cursor.getShort (i));
            } else if (c == double.class || c == Double.class) {
                field.set (o, cursor.getDouble (i));
            } else if (c.isEnum ()) {
                String v = cursor.getString (i);
                if (!isEmpty (v)) {
                    Object t = Enum.valueOf ((Class<? extends Enum>) c, v);
                    field.set (o, t);
                }
            } else if (java.util.Date.class.isAssignableFrom (c)) {
                String v = cursor.getString (i);
                ref.setDateValue (field, o, c, v);
            }
        }

        return o;
    }

    /**
     * 根据游标构建 {@link ITypedMap} 对象
     * @param cursor SQLite 游标
     * @return 构建完成的 ITypedMap 对象
     */
    private static ITypedMap build (Cursor cursor) {
        int count = cursor.getColumnCount ();
        ITypedMap map = new TypedHashMap ();
        for (int i = 0; i < count; i ++) {
            String name = cursor.getColumnName (i);
            switch (cursor.getType (i)) {
                case 1: // integer
                    map.put (name, cursor.getInt (i));
                    break;
                case 2: // float
                    map.put (name, cursor.getFloat (i));
                    break;
                case 3: // text
                    map.put (name, cursor.getString (i));
                    break;
                case 4: // blob
                    map.put (name, cursor.getBlob (i));
                    break;
                case 5: // null
                    // skip null value
                    break;
            }
        }
        return map;
    }

    private static boolean isEmpty (String text) {
        return text != null && !text.trim ().isEmpty ();
    }
}